package com.df.springmvc.system.staff.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLResultBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.supper.SupperDao;


@Repository
public class StaffDao extends SupperDao{
	private String message;
	
	/**
	 * *
	 * @param sql
	 *            查询的SQL
	 * @param param_HashMap
	 *            查询参数
	 * @param i
	 *            从第几天开始
	 * @param j
	 *            显示多少条数据
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Object> query(HashMap<String,String> map,Object[] attribute,int i,int j) throws Exception{
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		//String sql="select '' as 'ck',o.guid,o.staffName,(select exp.factoryName from b_factory_info exp where  exp.factoryCode =(select exp.pFactoryCode from b_department_info exp where  exp.departmentCode =o.departmentCode )) as 'factoryName',(select exp.departmentName from b_department_info exp where  exp.departmentCode =o.departmentCode) as 'departmentCode',(select expTabName from s_expandTable_info where status ='1' and expTabCode =o.jobCategoryCode ) as 'jobCategoryName',o.phone,o.email,o.major,(select exp.expTabName from s_expandTable_info exp where  exp.expTabCode =o.positionCode) as 'positionName' from b_staff_info o where 1=1 and o.status = 1  ";
		String sql="select '' as ck,o.guid,o.staffCode,o.staffNumCode,o.staffName,date_format(o.birthday,'%Y-%m-%d') as 'birthday',(select orgName from s_org_info where status ='1' and orgCode =o.orgCode ) as org,o.positions,o.telphone,o.telphone1,o.email,(case  when o.incumbencyStatus='1' then '在职' else '离职' END) as incumbencyStatus ,o.userAccount from b_staff_info o where 1=1 and o.status = 1   ";
		
		if(map.get("staffName") != null && !"".equals(map.get("staffName"))){
			sql=sql + " and o.staffName LIKE :s  ";
		}
		if(map.get("staffNumCode") != null && !"".equals(map.get("staffNumCode"))){
			sql=sql + " and o.staffNumCode LIKE :staffNumCode ";
		}
		sql = sql+" order by o.createTime desc";
		System.out.println(sql);
		SQLQuery query=session.createSQLQuery(sql);
		if(map.get("staffName") != null && !"".equals(map.get("staffName"))){
			query.setParameter("s","%" + map.get("staffName") + "%");
		}
		if(map.get("staffNumCode") != null && !"".equals(map.get("staffNumCode"))){
			query.setParameter("staffNumCode","%" + map.get("staffNumCode") + "%");
		}
		query.setFirstResult(i);
		query.setMaxResults(j);
		ArrayList<Object> list = (ArrayList<Object>) query.list();
		System.out.println(list.size());
		return (ArrayList<Object>)query.list();
	}
	/**
	 * *
	 * 添加的时候验证重复
	 * @param map
	 * @return
	 */
	public Object queryVerify(HashMap<String,String> map){
		
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from b_staff_info where staffNumCode=:staffNumCode and STATUS = 1";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("staffNumCode",map.get("staffNumCode"));
		if(query.list().size() > 0){
			message="有重复的员工工号，请重新输入！";
			return message;
		}else{
			Session session2=getSessionFactory().getCurrentSession();
			String sql2="select 0 from s_users_info where userAccount=:userAccount and STATUS = 1";
			SQLQuery query2=session2.createSQLQuery(sql2);
			query2.setString("userAccount",map.get("userAccount"));
			if(query2.list().size() > 0){
				message="有重复的用户账号，请重新输入！";
				return message;
			}
		}
		return null;
	}
	/**
	 * 修改的时候验重
	 * @param map
	 * @return
	 */
	public Object queryVerifyUpd(HashMap<String,String> map){
		Session session2=getSessionFactory().getCurrentSession();
		String sql2="select 0 from b_staff_info where staffNumCode=:staffNumCode and STATUS = 1 and guid not in (:gu)";
		SQLQuery query2=session2.createSQLQuery(sql2);
		query2.setString("staffNumCode",map.get("staffNumCode"));
		query2.setString("gu",map.get("guid"));
		if(query2.list().size() > 0){
			message="有重复的员工工号，请重新输入！";
			return message;
		}
		return null;
	}
	/**
	 * *
	 * 创建账号的时候验证重复
	 * @param map
	 * @return
	 */
	public Object queryVerifyAccount(HashMap<String,String> map){
		Session session2=getSessionFactory().getCurrentSession();
		String sql2="select 0 from s_users_info where userAccount=:userAccount and STATUS = 1";
		SQLQuery query2=session2.createSQLQuery(sql2);
		query2.setString("userAccount",map.get("userAccount"));
		if(query2.list().size() > 0){
			message="有重复的用户账号，请重新输入！";
			return message;
		}
		return null;
	}
	public Object query(HashMap<String,String> map){
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select * from b_staff_info where 1=1 and STATUS = 1 and guid=:g";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("g",map.get("guid"));
		return query.list();
	}
	@Override
	public Object add(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("b_staff_info");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object o=excuteSqlBean(insertBean);
		return o;
	}
	/**
	 * 创建账号
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object createAccount(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String staffNumCode = (String) map.get("staffNumCode");
		SQLInsertBean insertBean=new SQLInsertBean("s_users_info");
		map.remove("staffNumCode");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object o=excuteSqlBean(insertBean);
		//账号创建成功，修改员工表的账号信息
		if("1".equals(o)){
			HashMap<String,Object> tmp=new HashMap<String,Object>();
			tmp.put("staffNumCode",staffNumCode);
			tmp.put("incumbencyStatus","1");
			tmp.put("status","1");
			HashMap<String,Object> updatetmp=new HashMap<String,Object>();
			updatetmp.put("userAccount", map.get("userAccount"));
			Object rs=excuteSqlBean(new SQLUpdateBean("b_staff_info",updatetmp,tmp){});
			if("1".equals(rs)){
				return o;
			}else{
				throw new RuntimeException("创建失败！");
			}
		}
		return o;
	}
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("b_staff_info",map,tmp){});
		return rs;
	}
	/**
	 * 员工离职
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object leave(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String userAccount = (String) map.get("userAccount");
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("b_staff_info",map,tmp){});
		if("1".equals(rs)){
			//查询是否有账号
			ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserAccount((String)map.get("userAccount"));
			//账号存在，逻辑删除账号
			if(listQuery.size()>0){
				HashMap<String,Object> tmp2=new HashMap<String,Object>();
				map.remove("incumbencyStatus");
				map.remove("guid");
				map.put("status", "0");
				tmp2.put("userAccount",map.get("userAccount"));
				Object rs2=excuteSqlBean(new SQLUpdateBean("s_users_info",map,tmp2){});
				if("1".equals(rs2)){
					return rs;
				}else{
					throw new RuntimeException("修改失败！");
				}
			}
			
			
			
		}
		return rs;
	}
	/*private ArrayList<Object> VerifyUserAccount() {
		// TODO Auto-generated method stub
		return null;
	}*/
	@SuppressWarnings("unchecked")
	public  ArrayList<Object> VerifyUserAccount(String userAccount) {
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select guid from s_users_info where userAccount=:userAccount and STATUS = 1 ";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userAccount",userAccount);
		return (ArrayList<Object>) query.list();
	}
	@Override
	public HashMap<String,Object> view(String guid) throws Exception{
		// TODO Auto-generated method stub
		String sql="select o.*,(select orgName from s_org_info where status ='1' and orgCode =o.orgCode ) as orgName,(case  when o.incumbencyStatus='1' then '在职' else '离职' END) as incumbencyStatusName from b_staff_info as o  where status='1' and o.guid=:guid ";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	@Override
	public Object del(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		//Object rs=updateSql("delete b_staff_info where guid  =:guid",map);
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("b_staff_info",map,tmp){});
		return rs;
	}
	public boolean check(String guid) throws Exception {
		// TODO Auto-generated method stub
		String sql="select * from b_staff_info where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 0){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 获取参与人员信息
	 * 
	 * @param teamCode
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getStaffAll(String loginName) throws Exception {
		String sql = "SELECT staffNumCode,staffCode,staffName,userAccount FROM b_staff_info WHERE STATUS ='1' AND incumbencyStatus='1' ";
		return querySqlToSQLResultBean(sql).getMapList();

	}
	
	public String getMessage(){
		return message;
	}
	public void setMessage(String message){
		this.message=message;
	}
}
